package data;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import domain.*;
import utils.UIDGenerator;

public class JDBCRankingDAO implements IRankingDAO{
	
	private Connection conn;
	
	 public JDBCRankingDAO() {
         conn = ConnectionManager.getInstance().checkOut();
        
     }

	public List<Ranking> selectAllRanking(){
	
		  PreparedStatement stmt = null;
		  Connection conn = ConnectionManager.getInstance().checkOut();
		  List<Ranking> searchResults = new LinkedList();
		  ResultSet result = null;
		  
		  try {

	            String sql = "SELECT * FROM Ranking";
	            stmt = conn.prepareStatement(sql); //conn es una conexion valida
	            stmt.executeQuery();
	            result = stmt.executeQuery();

	            while (result.next()) {
	                Ranking temp = new Ranking();
	                temp.setId(result.getString("idr"));
	                temp.setRol1(result.getInt("rol1"));
	                temp.setRol1(result.getInt("rol2"));
	                temp.setRol1(result.getInt("rol3"));
	                temp.setRol1(result.getInt("rol4"));
	                temp.setRol1(result.getInt("rol5"));
	                searchResults.add(temp);
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (result != null)
	                    result.close();
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            }

	        }
	        return searchResults;
	    }
	

	public String insert(Ranking r){
		//insert raking
	    String oidr = UIDGenerator.getInstance().getKey();
	    PreparedStatement stmt = null;
	    String sql = "INSERT INTO ranking ( idr, rol1, rol2, rol3, rol4, rol5) VALUES (?, ?, ?, ?, ?, ?) ";
	    try {
	          stmt = conn.prepareStatement(sql);

	            stmt.setString(1, oidr);
	            stmt.setInt(2, r.getRol1());
	            stmt.setInt(3, r.getRol2());
	            stmt.setInt(4, r.getRol3());
	            stmt.setInt(5, r.getRol4());
	            stmt.setInt(6, r.getRol5());

	            stmt.executeUpdate();

	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return oidr;
	    }
		
	public void delete (String rakingID){
		 String sql = "DELETE FROM ranking WHERE (idr = ?) ";
	     PreparedStatement stmt = null;

	     String RankingOID = selectOIDRanking(rakingID);

	     if (RankingOID!=null) {
	        
	     try {
	         stmt = conn.prepareStatement(sql);
	         stmt.setString(1, RankingOID);
	         stmt.executeUpdate();
	     } catch (SQLException e) {
	         System.out.println("Message: " + e.getMessage());
	         System.out.println("SQLState: " + e.getSQLState());
	         System.out.println("ErrorCode: " + e.getErrorCode());
	     	} finally {
	     		try {
	             	if (stmt != null)
	                stmt.close();
	     		} catch (SQLException e) {
	         }
	   }
	   }
}	

	public String selectOIDRanking(String OIDranking){
		
		PreparedStatement stmt = null;
	    ResultSet result = null;
	    String oid = null;
	    String sql = "SELECT * FROM Ranking WHERE (idr = ?) ";
	
	    try {
	        stmt = conn.prepareStatement(sql);
	        stmt.setString(1, OIDranking);
	        result = stmt.executeQuery();
	
	        if (result.next()) {
	            oid = result.getString("OID");
	        }
	    } catch (SQLException e) {
	        System.out.println("Message: " + e.getMessage());
	        System.out.println("SQLState: " + e.getSQLState());
	        System.out.println("ErrorCode: " + e.getErrorCode());
	    } finally {
	        try {
	            if (result != null) {
	                result.close();
	            }
	            if (stmt != null) {
	                stmt.close();
	            }
	        } catch (SQLException e) {
	        }
	    }
	    return oid;
	}
	
}